package com.mao.dao.searchvoucher;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.hibernate.Hibernate;
import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.mao.action.jsonvo.searchcenter.JsonVoSearchProfit;
import com.mao.action.jsonvo.searchcenter.JsonVoSearchPurchaseByProduct;
import com.mao.action.jsonvo.searchcenter.JsonVoSearchVoucherByProduct;
import com.mao.action.jsonvo.searchcenter.JsonVoVoucherProduct;
import com.mao.dao.BaseDAOHibernate;
import com.mao.dao.product.ProductDao;
import com.mao.model.product.Product;
import com.mao.model.voucher.DeliveryVoucher;
import com.mao.model.voucher.ProductDeliveryVoucher;
import com.mao.model.voucher.ProductReturnDeliveryVoucher;
import com.mao.model.voucher.ProductReturnWareHouseVoucher;
import com.mao.model.voucher.ProductVoucherBase;
import com.mao.model.voucher.ProductWareHouseVoucher;
import com.mao.model.voucher.ReturnDeliveryVoucher;
import com.mao.model.voucher.ReturnWareHouseVoucher;
import com.mao.model.voucher.VoucherBase;
import com.mao.model.voucher.VoucherType;
import com.mao.model.voucher.VoucherVerityType;
import com.mao.model.voucher.WareHouseVoucher;
import com.mao.service.impl.searchvoucher.SearchStockCondition;
import com.mao.service.impl.searchvoucher.SearchVoucherCondition;
import com.mao.util.Validator;

@Component("searchVoucherDao")
public class SearchVoucherDao extends BaseDAOHibernate {

	private Integer page;
	
	private Integer rows;
	
	private ProductDao productDao;
	
	public List<VoucherBase> searchVoucher(Long storeid, Integer page,
			Integer rows, SearchVoucherCondition searchVoucherCondition) {
		StringBuilder hql = new StringBuilder();
		hql.append(" from " + searchVoucherCondition.getVoucherType().getVoucherClassName() + " vb where 1=1");
		// hql.append(" and vb.status<>'" + VoucherBase.STATUS_DELETED + "'");
		if (searchVoucherCondition != null) {
			if (!Validator.isEmpty(searchVoucherCondition.getOrgId())) {
				hql.append(" and vb.org.id = '" + searchVoucherCondition.getOrgId() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getVoucherNo())) {
				hql.append(" and vb.voucherNo like '%" + searchVoucherCondition.getVoucherNo() + "%'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and vb.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and vb.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getIsverity())) {
				hql.append(" and vb.isverity = '" + searchVoucherCondition.getIsverity() + "'");
			}
			if (!Validator.isEmpty(storeid)) {
				hql.append(" and vb.store.id = '" + storeid + "'");
			}
		}
		hql.append(" order by vb.datetime desc");
		Session session = this.getSession();
		Query query = session.createQuery(hql.toString());
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		List<VoucherBase> list = query.list();
		for (int i = 0; i < list.size(); i++) {
			Hibernate.initialize(list.get(i).getOrg());
			Hibernate.initialize(list.get(i).getWareHouse());
		}
		return list;
	}

	public VoucherBase getVoucherById(String voucherType, Long id) {
		String hql = "from " + voucherType + " vb where vb.id = :id";
		Session session = this.getSession();
		Query query = session.createQuery(hql);
		query.setParameter("id", id);
		List<VoucherBase> list = query.list();
		VoucherBase vb = null;
		if (list != null) {
			vb = list.get(0);
			Hibernate.initialize(vb.getOrg());
			Hibernate.initialize(vb.getWareHouse());
			Hibernate.initialize(vb.getUser());
			return vb;
		}
		return null;
	}

	public int searchVoucherCount(Long storeid, Integer page, Integer rows,
			SearchVoucherCondition searchVoucherCondition) {
		StringBuilder hql = new StringBuilder();
		hql.append(" select count(id) from " + searchVoucherCondition.getVoucherType().getVoucherClassName() + " vb where 1=1");
		// hql.append(" and vb.status<>'" + VoucherBase.STATUS_DELETED + "'");
		if (searchVoucherCondition != null) {
			if (!Validator.isEmpty(searchVoucherCondition.getOrgId())) {
				hql.append(" and vb.org.id = '" + searchVoucherCondition.getOrgId() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getVoucherNo())) {
				hql.append(" and vb.voucherNo like '%" + searchVoucherCondition.getVoucherNo() + "%'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and vb.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and vb.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getIsverity())) {
				hql.append(" and vb.isverity = '" + searchVoucherCondition.getIsverity() + "'");
			}
		}
		hql.append(" order by vb.datetime desc");
		Session session = this.getSession();
		Query query = session.createQuery(hql.toString());
		return ((Long) query.uniqueResult()).intValue();
	}

	public List<JsonVoVoucherProduct> searchProductsByVoucher(long voucherid, VoucherType voucherType) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("from " + voucherType.getProductVoucherClassName() + " pv where 1=1");
		hql.append(" and pv.voucher.id = :id");
		Query query = session.createQuery(hql.toString());
		query.setParameter("id", voucherid);
		List<ProductVoucherBase> list = query.list();
		if (list == null) {
			return null;
		}
		for (ProductVoucherBase pvb : list) {
			Hibernate.initialize(pvb.getProduct());
		}
		List<JsonVoVoucherProduct> plist = new ArrayList<JsonVoVoucherProduct>();
		JsonVoVoucherProduct jvvp = null;
		ProductVoucherBase pvb = null;
		Product pro = null;
		for (int i = 0; i < list.size(); i++) {
			jvvp = new JsonVoVoucherProduct();
			pvb = list.get(i);
			pro = pvb.getProduct();
			jvvp.setId(pro.getId());
			jvvp.setName(pro.getName());
			jvvp.setProNo(pro.getProNo());
			jvvp.setSpec(pro.getSpec());
			jvvp.setNote(pro.getNote());
			jvvp.setUnitPrice(pvb.getUnitPrice().toString());
			jvvp.setAmount(pvb.getAmount());
			jvvp.setSubtotal(pvb.getSubtotal().toString());
			plist.add(jvvp);
		}
		return plist;
	}

	public List<ProductVoucherBase> getPvbByPro(long productid, VoucherType voucherType) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("from " + voucherType.getProductVoucherClassName() + " pv where pv.product.id=:id");
		Query query = session.createQuery(hql.toString());
		query.setParameter("id", productid);
		List<ProductVoucherBase> list = query.list();
		return list;
	}

	// 查询统计-按商品
	public List<JsonVoSearchPurchaseByProduct> getSearchPurchaseByProduct(
			SearchVoucherCondition searchVoucherCondition, Integer page, Integer rows) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT pv.product.name , pv.product.spec , SUM(pv.subtotal),SUM(pv.amount) ,ROUND(AVG(pv.unitPrice),2),pv.product.id, pv.voucher.datetime,pv.product.proClass.name From ");
		hql.append(searchVoucherCondition.getVoucherType().getProductVoucherClassName() + " pv");
		hql.append(" where pv.voucher.isverity='" + VoucherVerityType.verityed + "'");
		hql.append(" and pv.store.id=" + searchVoucherCondition.getStoreid());
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pv.product.proClass.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchProName())){
			hql.append(" and pv.product.name like '%"+searchVoucherCondition.getSearchProName().trim()+"%'");
		}
		hql.append(" group by pv.product.id");
		Query query = session.createQuery(hql.toString());
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		List list = query.list();
		List<JsonVoSearchPurchaseByProduct> result = new ArrayList<JsonVoSearchPurchaseByProduct>();
		Iterator it = list.iterator();
		JsonVoSearchPurchaseByProduct jpp = null;
		while (it.hasNext()) {
			Object[] obj = (Object[]) it.next();
			jpp = new JsonVoSearchPurchaseByProduct();
			jpp.setName(String.valueOf(obj[0]));
			jpp.setSpec(String.valueOf(obj[1]));
			jpp.setId(Long.valueOf(obj[5].toString())); // productId;
			/* 查询退货 */
			// 判断是否是进货,要减掉退货的数量和金额
			// 退货处理开始
			StringBuilder returnWareHouseSql = new StringBuilder();
			returnWareHouseSql.append("SELECT SUM(prwv.amount),SUM(prwv.subtotal) FROM " + searchVoucherCondition.getVoucherType().getReturnProductVoucherClass() + " prwv WHERE prwv.product.id=" + jpp.getId());
			returnWareHouseSql.append(" and prwv.voucher.isverity='" + VoucherVerityType.verityed + "'");
			returnWareHouseSql.append(" and prwv.store.id=" + searchVoucherCondition.getStoreid());
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseSql.append(" and prwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseSql.append(" and prwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			Session returnWareHouseSession = this.getSession();
			Query returnWareHousequery = returnWareHouseSession.createQuery(returnWareHouseSql.toString());
			Object[] returnWhObj = (Object[])returnWareHousequery.list().get(0);
			BigDecimal totalSubTotal = new BigDecimal(0); // 进货总金额
			if(obj[2] != null) {
				totalSubTotal = BigDecimal.valueOf(Double.parseDouble(obj[2].toString()));
			}
			BigDecimal returnWhTotalSubTotal = new BigDecimal(0); // 退货总金额
			if(returnWhObj[1] != null){
				returnWhTotalSubTotal = BigDecimal.valueOf(Double.parseDouble(returnWhObj[1].toString()));
			}
			Integer totalAmount = 0; // 进货总数量
			if(obj[3] != null){
				totalAmount = Integer.parseInt(obj[3].toString());
			}
			Integer returnWhAmount = 0; // 退货总数量
			if(returnWhObj[0] != null){
				returnWhAmount = Integer.parseInt(returnWhObj[0].toString());
			}
			jpp.setReturnAmount(returnWhAmount);
			jpp.setReturnPrice(returnWhTotalSubTotal);
			if(jpp.getReturnAmount() == 0 || jpp.getReturnAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAverageReturnPrice(new BigDecimal(0));
			}else{
				jpp.setAverageReturnPrice(jpp.getReturnPrice().divide(new BigDecimal(jpp.getReturnAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			jpp.setTotalSubTotal(totalSubTotal.subtract(returnWhTotalSubTotal));
			jpp.setAmount(totalAmount - returnWhAmount); // 进货总金额减去进货退货总金额
			if(jpp.getAmount() == 0 || jpp.getAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAveragePrice(new BigDecimal(0));
			}else{
				jpp.setAveragePrice(jpp.getTotalSubTotal().divide(new BigDecimal(jpp.getAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			//退货处理结束
			jpp.setDatetime(String.valueOf(obj[6]));
			jpp.setProClassName(String.valueOf(obj[7]));
			result.add(jpp);
		}
		return result;
	}

	// 查询统计-按商品 分页总数
	public Integer getSearchByProductCount(SearchVoucherCondition searchVoucherCondition) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT pwv.product.name , pwv.product.spec , SUM(pwv.subtotal),SUM(pwv.amount) ,ROUND(AVG(pwv.unitPrice),2),pwv.product.id, pwv.voucher.datetime,pwv.product.proClass.name From ");
		hql.append(searchVoucherCondition.getVoucherType().getProductVoucherClassName() + " pwv");
		hql.append(" where pwv.voucher.isverity='" + VoucherVerityType.verityed + "'");
		hql.append(" and pwv.store.id=" + searchVoucherCondition.getStoreid());
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pwv.product.proClass.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchProName())){
			hql.append(" and pwv.product.name like '%"+searchVoucherCondition.getSearchProName().trim()+"%'");
		}
		hql.append(" group by pwv.product.id");
		Query query = session.createQuery(hql.toString());
		return query.list().size();
	}

	// 查询统计-按商品 明细查询
	public List<JsonVoSearchVoucherByProduct> searchVoucherByProduct(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "ProductWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ProductReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "ProductWareHouseVoucher";
			returnVoucherName = "ProductReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "ProductDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ProductReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "ProductDeliveryVoucher";
			returnVoucherName = "ProductReturnDeliveryVoucher";
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<JsonVoSearchVoucherByProduct> voucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		List<JsonVoSearchVoucherByProduct> returnVoucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		// 进出货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pwv");
			hql.append(" where pwv.product.id = " + searchVoucherCondition.getProId());
			hql.append(" and pwv.voucher.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pwv.voucher.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			List list = query.list();
			Iterator it = list.iterator();
			JsonVoSearchVoucherByProduct jsv = null;
			if (voucherName.equals(VoucherType.warehouse.getProductVoucherClassName())) {
				for (int i = 0; i < list.size(); i++) {
					ProductWareHouseVoucher pwv = (ProductWareHouseVoucher) list.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pwv.getVoucher().getId());
					jsv.setNo(pwv.getVoucher().getVoucherNo());
					jsv.setDate(sdf.format(pwv.getVoucher().getDatetime()));
					jsv.setOrg(pwv.getVoucher().getOrg().getName());
					jsv.setWarehouse(pwv.getVoucher().getWareHouse().getName());
					jsv.setUnitPrice(pwv.getUnitPrice());
					jsv.setSubtotal(pwv.getSubtotal());
					jsv.setAmount(pwv.getAmount());
					jsv.setVoucherType(VoucherType.warehouse.toString());
					voucherResult.add(jsv);
				}
			}
			if (voucherName.equals(VoucherType.delivery.getProductVoucherClassName())) {
				for (int i = 0; i < list.size(); i++) {
					ProductDeliveryVoucher pwv = (ProductDeliveryVoucher) list.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pwv.getVoucher().getId());
					jsv.setNo(pwv.getVoucher().getVoucherNo());
					jsv.setDate(sdf.format(pwv.getVoucher().getDatetime()));
					jsv.setOrg(pwv.getVoucher().getOrg().getName());
					jsv.setWarehouse(pwv.getVoucher().getWareHouse().getName());
					jsv.setUnitPrice(pwv.getUnitPrice());
					jsv.setSubtotal(pwv.getSubtotal());
					jsv.setAmount(pwv.getAmount());
					jsv.setVoucherType(VoucherType.delivery.toString());
					voucherResult.add(jsv);
				}
			}
		}
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prwv");
			returnWareHouseHql.append(" where prwv.product.id = " + searchVoucherCondition.getProId());
			returnWareHouseHql.append(" and prwv.voucher.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prwv.voucher.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			List returnWareHouseList = returnWareHouseQuery.list();
			if (returnVoucherName.equals(VoucherType.returnwarehouse.getProductVoucherClassName())) {
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ProductReturnWareHouseVoucher prwv = (ProductReturnWareHouseVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prwv.getVoucher().getId());
					rjv.setNo(prwv.getVoucher().getVoucherNo());
					rjv.setDate(sdf.format(prwv.getVoucher().getDatetime()));
					rjv.setOrg(prwv.getVoucher().getOrg().getName());
					rjv.setWarehouse(prwv.getVoucher().getWareHouse().getName());
					rjv.setUnitPrice(prwv.getUnitPrice());
					rjv.setSubtotal(prwv.getSubtotal());
					rjv.setAmount(prwv.getAmount());
					rjv.setVoucherType(VoucherType.returnwarehouse.toString());
					returnVoucherResult.add(rjv);
				}
			}
			if (returnVoucherName.equals(VoucherType.returndelivery.getProductVoucherClassName())) {
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ProductReturnDeliveryVoucher prwv = (ProductReturnDeliveryVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prwv.getVoucher().getId());
					rjv.setNo(prwv.getVoucher().getVoucherNo());
					rjv.setDate(sdf.format(prwv.getVoucher().getDatetime()));
					rjv.setOrg(prwv.getVoucher().getOrg().getName());
					rjv.setWarehouse(prwv.getVoucher().getWareHouse().getName());
					rjv.setUnitPrice(prwv.getUnitPrice());
					rjv.setSubtotal(prwv.getSubtotal());
					rjv.setAmount(prwv.getAmount());
					rjv.setVoucherType(VoucherType.returndelivery.toString());
					returnVoucherResult.add(rjv);
				}
			}
			
		}
		voucherResult.addAll(returnVoucherResult);
		// 排序按时间
		Collections.sort(voucherResult, new Comparator<JsonVoSearchVoucherByProduct>() {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
			public int compare(JsonVoSearchVoucherByProduct o1, JsonVoSearchVoucherByProduct o2) {
				Date d1 = null;
				Date d2 = null;
				try {
					d1 = sdf.parse(o1.getDate());
					d2 = sdf.parse(o2.getDate());
				} catch (ParseException e) {
					e.printStackTrace();
				}
	            if (d1 == null && d2 == null)
	                return 0;
	            if (d1 == null)
	                return -1;
	            if (d2 == null)
	                return 1;
	            return d2.compareTo(d1); 
			 }
		});
		List<JsonVoSearchVoucherByProduct> result = null;
		Integer count = searchVoucherByProductCount(searchVoucherCondition);
		setPageNum(page,rows,count);
		result = voucherResult.subList(this.page, this.rows);
		return result;
	}
	
	// 设置list的sublist的分页参数
	public void setPageNum(Integer page,Integer rows,Integer count) {
		this.page = (page - 1) * rows;
		// 第一页
		if(rows > count){
			this.rows = count; 
			return;
		}
		// 最后页
		if(rows*page > count){
			this.rows = count;
			return;
		}
		this.rows = page * rows;
	}

	// 查询统计-按商品 明细查询 分页总数
	public Integer searchVoucherByProductCount(
			SearchVoucherCondition searchVoucherCondition) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "ProductWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ProductReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "ProductWareHouseVoucher";
			returnVoucherName = "ProductReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "ProductDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ProductReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "ProductDeliveryVoucher";
			returnVoucherName = "ProductReturnDeliveryVoucher";
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List list = new ArrayList();
		// 进出货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pwv");
			hql.append(" where pwv.product.id = " + searchVoucherCondition.getProId());
			hql.append(" and pwv.voucher.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pwv.voucher.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			list = query.list();
		}
		List<ProductReturnWareHouseVoucher> returnWareHouseList = new ArrayList<ProductReturnWareHouseVoucher>();
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prwv");
			returnWareHouseHql.append(" where prwv.product.id = " + searchVoucherCondition.getProId());
			returnWareHouseHql.append(" and prwv.voucher.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prwv.voucher.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prwv.voucher.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prwv.voucher.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			returnWareHouseList = returnWareHouseQuery.list();
		}
		list.addAll(returnWareHouseList);
		return list.size();
	}

	// 统计查询 - 按供应商
	public List<JsonVoSearchPurchaseByProduct> getSearchPurchaseByOrg(
			SearchVoucherCondition searchVoucherCondition, Integer page, Integer rows) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT pv.org.name , SUM(pv.transactionAmount) , SUM(pv.proAmount) ,ROUND(AVG(pv.transactionAmount),2),pv.org.id,pv.org.oc.name From ");
		hql.append(searchVoucherCondition.getVoucherType().getVoucherClassName() + " pv");
		hql.append(" where pv.isverity='" + VoucherVerityType.verityed + "'");
		hql.append(" and pv.store.id=" + searchVoucherCondition.getStoreid());
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pv.org.oc.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchOrgName())){
			hql.append(" and pv.org.name like '%"+searchVoucherCondition.getSearchOrgName().trim()+"%'");
		}
		
		hql.append(" group by pv.org.id");
		Query query = session.createQuery(hql.toString());
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		List list = query.list();
		List<JsonVoSearchPurchaseByProduct> result = new ArrayList<JsonVoSearchPurchaseByProduct>();
		Iterator it = list.iterator();
		JsonVoSearchPurchaseByProduct jpp = null;
		while (it.hasNext()) {
			Object[] obj = (Object[]) it.next();
			jpp = new JsonVoSearchPurchaseByProduct();
			jpp.setName(String.valueOf(obj[0]));
			jpp.setTotalSubTotal(BigDecimal.valueOf(Double.parseDouble(obj[1].toString())));
			jpp.setId(Long.valueOf(String.valueOf(obj[4])));
			/* 查询退货 */
			// 判断是否是进货,要减掉退货的数量和金额
			StringBuilder returnWareHouseSql = new StringBuilder();
			returnWareHouseSql.append("SELECT SUM(prv.proAmount),SUM(prv.transactionAmount) FROM " + searchVoucherCondition.getVoucherType().getReturnVoucherClass() + " prv WHERE prv.org.id=" + jpp.getId());
			returnWareHouseSql.append(" and prv.isverity='" + VoucherVerityType.verityed + "'");
			returnWareHouseSql.append(" and prv.store.id=" + searchVoucherCondition.getStoreid());
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseSql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseSql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			Session returnWareHouseSession = this.getSession();
			Query returnWareHousequery = returnWareHouseSession.createQuery(returnWareHouseSql.toString());
			Object[] returnWhObj = (Object[])returnWareHousequery.list().get(0);
			BigDecimal totalSubTotal = new BigDecimal(0); // 进货总金额
			if(obj[1] != null) {
				totalSubTotal = BigDecimal.valueOf(Double.parseDouble(obj[1].toString()));
			}
			BigDecimal returnWhTotalSubTotal = new BigDecimal(0); // 退货总金额
			if(returnWhObj[1] != null){
				returnWhTotalSubTotal = BigDecimal.valueOf(Double.parseDouble(returnWhObj[1].toString()));
			}
			Integer totalAmount = 0; // 进货总数量
			if(obj[2] != null){
				totalAmount = Integer.parseInt(obj[2].toString());
			}
			Integer returnWhAmount = 0; // 退货总数量
			if(returnWhObj[0] != null){
				returnWhAmount = Integer.parseInt(returnWhObj[0].toString());
			}
			jpp.setReturnAmount(returnWhAmount);
			jpp.setReturnPrice(returnWhTotalSubTotal);
			if(jpp.getReturnAmount() == 0 || jpp.getReturnAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAverageReturnPrice(new BigDecimal(0));
			}else{
				jpp.setAverageReturnPrice(jpp.getReturnPrice().divide(new BigDecimal(jpp.getReturnAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			jpp.setTotalSubTotal(totalSubTotal.subtract(returnWhTotalSubTotal));
			jpp.setAmount(totalAmount - returnWhAmount); // 进货总金额减去进货退货总金额
			if(jpp.getAmount() == 0 || jpp.getAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAveragePrice(new BigDecimal(0));
			}else{
				jpp.setAveragePrice(jpp.getTotalSubTotal().divide(new BigDecimal(jpp.getAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			// 查询退货结束
			jpp.setOrgClassName(String.valueOf(obj[5]));
			result.add(jpp);
		}
		return result;
	}

	public Integer getSearchPurchaseByOrgCount(
			SearchVoucherCondition searchVoucherCondition) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT pv.org.name , SUM(pv.transactionAmount) , SUM(pv.proAmount) ,ROUND(AVG(pv.transactionAmount),2),pv.org.id,pv.org.oc.name From ");
		hql.append(searchVoucherCondition.getVoucherType().getVoucherClassName() + " pv");
		hql.append(" where pv.isverity='" + VoucherVerityType.verityed + "'");
		hql.append(" and pv.store.id=" + searchVoucherCondition.getStoreid());
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pv.org.oc.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchOrgName())){
			hql.append(" and pv.org.name like '%"+searchVoucherCondition.getSearchOrgName().trim()+"%'");
		}
		hql.append(" group by pv.org.id");
		Query query = session.createQuery(hql.toString());
		List list = query.list();
		return list.size();
	}

	// 按往来单位查询-详情
	public List<JsonVoSearchVoucherByProduct> searchVoucherByOrg(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "WareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "WareHouseVoucher";
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "DeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "DeliveryVoucher";
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<JsonVoSearchVoucherByProduct> voucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		List<JsonVoSearchVoucherByProduct> returnVoucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		// 进货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pv");
			hql.append(" where pv.org.id = " + searchVoucherCondition.getOrgId());
			hql.append(" and pv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pv.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			List list = query.list();
			Iterator it = list.iterator();
			JsonVoSearchVoucherByProduct jsv = null;
			if (voucherName.equals(VoucherType.warehouse.getVoucherClassName())) {
				for (int i = 0; i < list.size(); i++) {
					WareHouseVoucher pv = (WareHouseVoucher) list.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pv.getId());
					jsv.setNo(pv.getVoucherNo());
					jsv.setDate(sdf.format(pv.getDatetime()));
					jsv.setOrg(pv.getOrg().getName());
					jsv.setWarehouse(pv.getWareHouse().getName());
					jsv.setTransactionAmount(pv.getTransactionAmount());
					jsv.setAmount(pv.getProAmount());
					jsv.setVoucherType(VoucherType.warehouse.toString());
					voucherResult.add(jsv);
				}
			}
			if (voucherName.equals(VoucherType.delivery.getVoucherClassName())) {
				for (int i = 0; i < list.size(); i++) {
					DeliveryVoucher pv = (DeliveryVoucher) list.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pv.getId());
					jsv.setNo(pv.getVoucherNo());
					jsv.setDate(sdf.format(pv.getDatetime()));
					jsv.setOrg(pv.getOrg().getName());
					jsv.setWarehouse(pv.getWareHouse().getName());
					jsv.setTransactionAmount(pv.getTransactionAmount());
					jsv.setAmount(pv.getProAmount());
					jsv.setVoucherType(VoucherType.delivery.toString());
					voucherResult.add(jsv);
				}
			}
		}
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prv");
			returnWareHouseHql.append(" where prv.org.id = " + searchVoucherCondition.getOrgId());
			returnWareHouseHql.append(" and prv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prv.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			List returnWareHouseList = returnWareHouseQuery.list();
			if (returnVoucherName.equals(VoucherType.returnwarehouse.getVoucherClassName())) {
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ReturnWareHouseVoucher prv = (ReturnWareHouseVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prv.getId());
					rjv.setNo(prv.getVoucherNo());
					rjv.setDate(sdf.format(prv.getDatetime()));
					rjv.setOrg(prv.getOrg().getName());
					rjv.setWarehouse(prv.getWareHouse().getName());
					rjv.setTransactionAmount(prv.getTransactionAmount());
					rjv.setAmount(prv.getProAmount());
					rjv.setVoucherType(VoucherType.returnwarehouse.toString());
					returnVoucherResult.add(rjv);
				}
			}
			if (returnVoucherName.equals(VoucherType.returndelivery.getVoucherClassName())) {
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ReturnDeliveryVoucher prv = (ReturnDeliveryVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prv.getId());
					rjv.setNo(prv.getVoucherNo());
					rjv.setDate(sdf.format(prv.getDatetime()));
					rjv.setOrg(prv.getOrg().getName());
					rjv.setWarehouse(prv.getWareHouse().getName());
					rjv.setTransactionAmount(prv.getTransactionAmount());
					rjv.setAmount(prv.getProAmount());
					rjv.setVoucherType(VoucherType.returndelivery.toString());
					returnVoucherResult.add(rjv);
				}
			}
		}
		voucherResult.addAll(returnVoucherResult);
		// 排序按时间
		Collections.sort(voucherResult, new Comparator<JsonVoSearchVoucherByProduct>() {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
			public int compare(JsonVoSearchVoucherByProduct o1, JsonVoSearchVoucherByProduct o2) {
				Date d1 = null;
				Date d2 = null;
				try {
					d1 = sdf.parse(o1.getDate());
					d2 = sdf.parse(o2.getDate());
				} catch (ParseException e) {
					e.printStackTrace();
				}
	            if (d1 == null && d2 == null)
	                return 0;
	            if (d1 == null)
	                return -1;
	            if (d2 == null)
	                return 1;
	            return d2.compareTo(d1); 
			 }
		});
		List<JsonVoSearchVoucherByProduct> result = null;
		Integer count = searchVoucherByOrgCount(searchVoucherCondition);
		setPageNum(page,rows,count);
		result = voucherResult.subList(this.page, this.rows);
		return result;
	}

	// 按供应商查询-详情 分页总数
	public Integer searchVoucherByOrgCount(
			SearchVoucherCondition searchVoucherCondition) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "WareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "WareHouseVoucher";
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "DeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "DeliveryVoucher";
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List list = new ArrayList();
		// 进出货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pv");
			hql.append(" where pv.org.id = " + searchVoucherCondition.getOrgId());
			hql.append(" and pv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pv.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			list = query.list();
		}
		List<ProductReturnWareHouseVoucher> returnWareHouseList = new ArrayList<ProductReturnWareHouseVoucher>();
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prv");
			returnWareHouseHql.append(" where prv.org.id = " + searchVoucherCondition.getOrgId());
			returnWareHouseHql.append(" and prv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prv.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			returnWareHouseList = returnWareHouseQuery.list();
		}
		list.addAll(returnWareHouseList);
		return list.size();
	}

	// 查询统计-按仓库
	public List<JsonVoSearchPurchaseByProduct> getSearchPurchaseByWH(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("SELECT pv.wareHouse.name , SUM(pv.transactionAmount) , SUM(pv.proAmount) ,ROUND(AVG(pv.transactionAmount),2),pv.wareHouse.id,pv.wareHouse.wc.name From ");
		hql.append(searchVoucherCondition.getVoucherType().getVoucherClassName() + " pv");
		hql.append(" where pv.isverity='" + VoucherVerityType.verityed + "'");
		hql.append(" and pv.store.id=" + searchVoucherCondition.getStoreid());
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pv.wareHouse.wc.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchWhName())){
			hql.append(" and pv.wareHouse.name like '%"+searchVoucherCondition.getSearchWhName().trim()+"%'");
		}
		
		hql.append(" group by pv.wareHouse.id");
		Query query = session.createQuery(hql.toString());
		query.setFirstResult((page - 1) * rows);
		query.setMaxResults(rows);
		List list = query.list();
		List<JsonVoSearchPurchaseByProduct> result = new ArrayList<JsonVoSearchPurchaseByProduct>();
		Iterator it = list.iterator();
		JsonVoSearchPurchaseByProduct jpp = null;
		while (it.hasNext()) {
			Object[] obj = (Object[]) it.next();
			jpp = new JsonVoSearchPurchaseByProduct();
			jpp.setName(String.valueOf(obj[0]));
			jpp.setTotalSubTotal(BigDecimal.valueOf(Double.parseDouble(obj[1].toString())));
			jpp.setId(Long.valueOf(String.valueOf(obj[4])));
			/* 查询退货 */
			// 判断是否是进货,要减掉退货的数量和金额
			StringBuilder returnWareHouseSql = new StringBuilder();
			returnWareHouseSql.append("SELECT SUM(prv.proAmount),SUM(prv.transactionAmount) FROM " + searchVoucherCondition.getVoucherType().getReturnVoucherClass() +" prv WHERE prv.wareHouse.id=" + jpp.getId());
			returnWareHouseSql.append(" and prv.isverity='" + VoucherVerityType.verityed + "'");
			returnWareHouseSql.append(" and prv.store.id=" + searchVoucherCondition.getStoreid());
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseSql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseSql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			Session returnWareHouseSession = this.getSession();
			Query returnWareHousequery = returnWareHouseSession.createQuery(returnWareHouseSql.toString());
			Object[] returnWhObj = (Object[])returnWareHousequery.list().get(0);
			BigDecimal totalSubTotal = new BigDecimal(0); // 进货总金额
			if(obj[1] != null) {
				totalSubTotal = BigDecimal.valueOf(Double.parseDouble(obj[1].toString()));
			}
			BigDecimal returnWhTotalSubTotal = new BigDecimal(0); // 退货总金额
			if(returnWhObj[1] != null){
				returnWhTotalSubTotal = BigDecimal.valueOf(Double.parseDouble(returnWhObj[1].toString()));
			}
			Integer totalAmount = 0; // 进货总数量
			if(obj[2] != null){
				totalAmount = Integer.parseInt(obj[2].toString());
			}
			Integer returnWhAmount = 0; // 退货总数量
			if(returnWhObj[0] != null){
				returnWhAmount = Integer.parseInt(returnWhObj[0].toString());
			}
			jpp.setReturnAmount(returnWhAmount);
			jpp.setReturnPrice(returnWhTotalSubTotal);
			if(jpp.getReturnAmount() == 0 || jpp.getReturnAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAverageReturnPrice(new BigDecimal(0));
			}else{
				jpp.setAverageReturnPrice(jpp.getReturnPrice().divide(new BigDecimal(jpp.getReturnAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			jpp.setTotalSubTotal(totalSubTotal.subtract(returnWhTotalSubTotal));
			jpp.setAmount(totalAmount - returnWhAmount); // 进货总金额减去进货退货总金额
			if(jpp.getAmount() == 0 || jpp.getAmount() == null){ // 不判断数量为0会报除以0错误
				jpp.setAveragePrice(new BigDecimal(0));
			}else{
				jpp.setAveragePrice(jpp.getTotalSubTotal().divide(new BigDecimal(jpp.getAmount()),10,BigDecimal.ROUND_HALF_DOWN));
			}
			// 查询退货结束
			jpp.setWhClassName(String.valueOf(obj[5]));
			result.add(jpp);
		}
		return result;
	}

	public Integer getSearchPurchaseByWHCount(SearchVoucherCondition searchVoucherCondition) {
		Session session = this.getSession();
		StringBuilder hql = new StringBuilder();
		hql.append("FROM " + searchVoucherCondition.getVoucherType().getVoucherClassName() + " pv ");
		hql.append("where pv.isverity= '" + VoucherVerityType.verityed+ "'");
		hql.append(" and pv.store.id=" + searchVoucherCondition.getStoreid());
		
		if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
			hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
		}
		if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
			hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			hql.append(" and pv.wareHouse.wc.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchWhName())){
			hql.append(" and pv.wareHouse.name like '%"+searchVoucherCondition.getSearchWhName().trim()+"%'");
		}
		hql.append(" group by pv.wareHouse.id");
		Query query = session.createQuery(hql.toString());
		return query.list().size();
	}

	// 查询统计按照仓库查单据 详情
	public List<JsonVoSearchVoucherByProduct> searchVoucherByWH(
			SearchVoucherCondition searchVoucherCondition, Integer page, Integer rows) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "WareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "WareHouseVoucher";
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "DeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "DeliveryVoucher";
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<JsonVoSearchVoucherByProduct> voucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		List<JsonVoSearchVoucherByProduct> returnVoucherResult = new ArrayList<JsonVoSearchVoucherByProduct>();
		// 进出货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pv");
			hql.append(" where pv.wareHouse.id = " + searchVoucherCondition.getWhId());
			hql.append(" and pv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pv.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			List list = query.list();
			Iterator it = list.iterator();
			JsonVoSearchVoucherByProduct jsv = null;
			if(voucherName.equals(VoucherType.warehouse.getVoucherClassName())){
				for (int i = 0; i < list.size(); i++) {
					WareHouseVoucher pv = (WareHouseVoucher) list
							.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pv.getId());
					jsv.setNo(pv.getVoucherNo());
					jsv.setDate(sdf.format(pv.getDatetime()));
					jsv.setOrg(pv.getOrg().getName());
					jsv.setWarehouse(pv.getWareHouse().getName());
					jsv.setTransactionAmount(pv.getTransactionAmount());
					jsv.setAmount(pv.getProAmount());
					jsv.setVoucherType(VoucherType.warehouse.toString());
					voucherResult.add(jsv);
				}
			}
			if(voucherName.equals(VoucherType.delivery.getVoucherClassName())){
				for (int i = 0; i < list.size(); i++) {
					DeliveryVoucher pv = (DeliveryVoucher) list.get(i);
					jsv = new JsonVoSearchVoucherByProduct();
					jsv.setId(pv.getId());
					jsv.setNo(pv.getVoucherNo());
					jsv.setDate(sdf.format(pv.getDatetime()));
					jsv.setOrg(pv.getOrg().getName());
					jsv.setWarehouse(pv.getWareHouse().getName());
					jsv.setTransactionAmount(pv.getTransactionAmount());
					jsv.setAmount(pv.getProAmount());
					jsv.setVoucherType(VoucherType.delivery.toString());
					voucherResult.add(jsv);
				}
			}
			
		}
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prv");
			returnWareHouseHql.append(" where prv.wareHouse.id = " + searchVoucherCondition.getWhId());
			returnWareHouseHql.append(" and prv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prv.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			List returnWareHouseList = returnWareHouseQuery.list();
			if(returnVoucherName.equals(VoucherType.returnwarehouse.getVoucherClassName())){
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ReturnWareHouseVoucher prv = (ReturnWareHouseVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prv.getId());
					rjv.setNo(prv.getVoucherNo());
					rjv.setDate(sdf.format(prv.getDatetime()));
					rjv.setOrg(prv.getOrg().getName());
					rjv.setWarehouse(prv.getWareHouse().getName());
					rjv.setTransactionAmount(prv.getTransactionAmount());
					rjv.setAmount(prv.getProAmount());
					rjv.setVoucherType(VoucherType.returnwarehouse.toString());
					returnVoucherResult.add(rjv);
				}
			}
			if(returnVoucherName.equals(VoucherType.returndelivery.getVoucherClassName())){
				for (int i = 0; i < returnWareHouseList.size(); i++) {
					ReturnDeliveryVoucher prv = (ReturnDeliveryVoucher) returnWareHouseList.get(i);
					JsonVoSearchVoucherByProduct rjv = null;
					rjv = new JsonVoSearchVoucherByProduct();
					rjv.setId(prv.getId());
					rjv.setNo(prv.getVoucherNo());
					rjv.setDate(sdf.format(prv.getDatetime()));
					rjv.setOrg(prv.getOrg().getName());
					rjv.setWarehouse(prv.getWareHouse().getName());
					rjv.setTransactionAmount(prv.getTransactionAmount());
					rjv.setAmount(prv.getProAmount());
					rjv.setVoucherType(VoucherType.returndelivery.toString());
					returnVoucherResult.add(rjv);
				}
			}
			
		}
		voucherResult.addAll(returnVoucherResult);
		// 排序按时间
		Collections.sort(voucherResult, new Comparator<JsonVoSearchVoucherByProduct>() {
			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss"); 
			public int compare(JsonVoSearchVoucherByProduct o1, JsonVoSearchVoucherByProduct o2) {
				Date d1 = null;
				Date d2 = null;
				try {
					d1 = sdf.parse(o1.getDate());
					d2 = sdf.parse(o2.getDate());
				} catch (ParseException e) {
					e.printStackTrace();
				}
	            if (d1 == null && d2 == null)
	                return 0;
	            if (d1 == null)
	                return -1;
	            if (d2 == null)
	                return 1;
	            return d2.compareTo(d1); 
			 }
		});
		List<JsonVoSearchVoucherByProduct> result = null;
		Integer count = searchVoucherByWHCount(searchVoucherCondition);
		setPageNum(page,rows,count);
		result = voucherResult.subList(this.page, this.rows);
		return result;
	}

	public Integer searchVoucherByWHCount(
			SearchVoucherCondition searchVoucherCondition) {
		String voucherName = null;
		String returnVoucherName = null;
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse.toString())) {
			voucherName = "WareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returnwarehouse.toString())) {
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.warehouse + "," + VoucherType.returnwarehouse)) {
			voucherName = "WareHouseVoucher";
			returnVoucherName = "ReturnWareHouseVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery.toString())) {
			voucherName = "DeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.returndelivery.toString())) {
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		if (searchVoucherCondition.getQueryVoucherType().equals(VoucherType.delivery + "," +VoucherType.returndelivery)) {
			voucherName = "DeliveryVoucher";
			returnVoucherName = "ReturnDeliveryVoucher";
		}
		
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List list = new ArrayList();
		// 进出货单
		if(voucherName != null){
			StringBuilder hql = new StringBuilder();
			hql.append(" from " + voucherName + " pv");
			hql.append(" where pv.wareHouse.id = " + searchVoucherCondition.getWhId());
			hql.append(" and pv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				hql.append(" and pv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				hql.append(" and pv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			hql.append(" order by pv.datetime desc");
			Session session = this.getSession();
			Query query = session.createQuery(hql.toString());
			list = query.list();
		}
		List<ProductReturnWareHouseVoucher> returnWareHouseList = new ArrayList<ProductReturnWareHouseVoucher>();
		// 退货单
		if(returnVoucherName != null){
			StringBuilder returnWareHouseHql = new StringBuilder();
			returnWareHouseHql.append(" from " + returnVoucherName + " prv");
			returnWareHouseHql.append(" where prv.wareHouse.id = " + searchVoucherCondition.getWhId());
			returnWareHouseHql.append(" and prv.isverity = '" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				returnWareHouseHql.append(" and prv.datetime >= '" + searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				returnWareHouseHql.append(" and prv.datetime <= '" + searchVoucherCondition.getEtime() + "'");
			}
			returnWareHouseHql.append(" order by prv.datetime desc");
			Session returnWareHouseSession = this.getSession();
			Query returnWareHouseQuery = returnWareHouseSession.createQuery(returnWareHouseHql.toString());
			returnWareHouseList = returnWareHouseQuery.list();
		}
		list.addAll(returnWareHouseList);
		return list.size();
	}

	// 盈利查询
	public List<JsonVoSearchProfit> searchProfit(
			SearchVoucherCondition searchVoucherCondition, Integer page,
			Integer rows) {
		Session session = this.getSession();
		StringBuilder proHql = new StringBuilder();
		// 查询该店铺所有商品
		proHql.append("from Product t where 1=1 and t.status='" + Product.STATUS_NORMAL + "'");
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			proHql.append(" and t.proClass.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchProName())){
			proHql.append(" and t.name like '%"+searchVoucherCondition.getSearchProName().trim()+"%'");
		}
		proHql.append(" and t.store.id=" + searchVoucherCondition.getStoreid());
		Query proQuery = session.createQuery(proHql.toString());
		proQuery.setFirstResult((page - 1) * rows);
		proQuery.setMaxResults(rows);
		List<Product> proList = proQuery.list();
		JsonVoSearchProfit jvsp = null;
		List<JsonVoSearchProfit> result = new ArrayList<JsonVoSearchProfit>();
		for (Product p : proList) {
			jvsp = new JsonVoSearchProfit();
			jvsp.setProName(p.getName());
			jvsp.setProClassName(p.getProClass().getName());
			jvsp.setProNo(p.getProNo());
			jvsp.setProSpec(p.getSpec());
			StringBuilder costHql = new StringBuilder();
			// 查进货均价和进货金额
			costHql.append("SELECT SUM(t.amount),SUM(t.subtotal) FROM ProductWareHouseVoucher t WHERE t.product.id=" + p.getId());
			costHql.append(" and t.voucher.isverity= '" + VoucherVerityType.verityed + "'");
			// 进货均价的截至时间
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				costHql.append(" and t.voucher.datetime <= '"
						+ searchVoucherCondition.getEtime() + "'");
			}
			Query costQuery = session.createQuery(costHql.toString());
			List costList = costQuery.list();
			Iterator cit = costList.iterator();
			while (cit.hasNext()) {
				Object[] obj = (Object[]) cit.next();
				if (obj[0] != null) {
					jvsp.setCostNum(Integer.parseInt(obj[0].toString())); // 进货数量
				}else{
					jvsp.setCostNum(0); // 为空则设为0
				}
				if (obj[1] != null) {
					jvsp.setCostPrice((BigDecimal) obj[1]); // 进货总金额
				}else{
					jvsp.setCostPrice(new BigDecimal(0));
				}
			}
			// 进货退货数量和金额
			StringBuilder reCostHql = new StringBuilder();
			reCostHql.append("SELECT SUM(t.amount),SUM(t.subtotal) FROM ProductReturnWareHouseVoucher t where t.product.id=" + p.getId());
			reCostHql.append(" and t.voucher.isverity='" + VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				reCostHql.append(" and t.voucher.datetime <= '"
						+ searchVoucherCondition.getEtime() + "'");
			}
			Query reCostQuery = session.createQuery(reCostHql.toString());
			List reCostList = reCostQuery.list();
			Iterator recit = reCostList.iterator();
			while(recit.hasNext()){
				Object[] obj = (Object[]) recit.next();
				if(obj[0] != null){
					jvsp.setReCostAmount(Integer.parseInt(obj[0].toString()));
				}else{
					jvsp.setReCostAmount(0);
				}
				if(obj[1] != null){
					jvsp.setReCostPrice((BigDecimal) obj[1]);
				}else{
					jvsp.setReCostPrice(new BigDecimal(0));
				}
			}
			// 查销售退货总数和销售退货金额
			StringBuilder resaleHql = new StringBuilder();
			resaleHql.append("SELECT SUM(t.amount),SUM(t.subtotal) FROM ProductReturnDeliveryVoucher t where t.product.id="
					+ p.getId());
			resaleHql.append(" and t.voucher.isverity= '"
					+ VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				resaleHql.append(" and t.voucher.datetime >= '"
						+ searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				resaleHql.append(" and t.voucher.datetime <= '"
						+ searchVoucherCondition.getEtime() + "'");
			}
			Query reSaleQuery = session.createQuery(resaleHql.toString());
			List reSaleList = reSaleQuery.list();
			Iterator reSit = reSaleList.iterator();
			while (reSit.hasNext()) {
				Object[] obj = (Object[]) reSit.next();
				if (obj[0] != null) {
					jvsp.setReSaleAmount(Integer.parseInt(obj[0].toString())); // 销售退货数量
				}else{
					jvsp.setReSaleAmount(0);
				}
				if (obj[1] != null) {
					jvsp.setReSalePrice((BigDecimal)obj[1]); // 销售退货金额
				}else{
					jvsp.setReSalePrice(new BigDecimal(0));
				}
			}
			// 查销售总数和销售金额
			StringBuilder saleHql = new StringBuilder();
			saleHql.append("SELECT SUM(t.amount),SUM(t.subtotal) FROM ProductDeliveryVoucher t where t.product.id="
					+ p.getId());
			saleHql.append(" and t.voucher.isverity= '"
					+ VoucherVerityType.verityed + "'");
			if (!Validator.isEmpty(searchVoucherCondition.getStime())) {
				saleHql.append(" and t.voucher.datetime >= '"
						+ searchVoucherCondition.getStime() + "'");
			}
			if (!Validator.isEmpty(searchVoucherCondition.getEtime())) {
				saleHql.append(" and t.voucher.datetime <= '"
						+ searchVoucherCondition.getEtime() + "'");
			}
			Query saleQuery = session.createQuery(saleHql.toString());
			List saleList = saleQuery.list();
			Iterator sit = saleList.iterator();
			while (sit.hasNext()) {
				Object[] obj = (Object[]) sit.next();
				if (obj[0] != null) {
					jvsp.setSaleNum(Integer.parseInt(obj[0].toString())); // 销售数量
				}else{
					jvsp.setSaleNum(0);
				}
				if (obj[1] != null) {
					jvsp.setSalePrice((BigDecimal)obj[1]); // 销售金额
				}else{
					jvsp.setSalePrice(new BigDecimal(0));
				}
			}
			Integer saleNum = 0;
			if(jvsp.getReSaleAmount() != null){
				saleNum = jvsp.getSaleNum() - jvsp.getReSaleAmount(); // 实际销售数量=销售总量-销售退货总量
			}
			Integer costNum = 0;
			if(jvsp.getReCostAmount() != null){
				costNum = jvsp.getCostNum() - jvsp.getReCostAmount(); // 实际进货数量=进货总量-进货退货总量
			}
			BigDecimal salePrice = new BigDecimal(0); 
			if(jvsp.getReSalePrice() != null){
				salePrice = jvsp.getSalePrice().subtract(jvsp.getReSalePrice());// 实际销售金额=销售总额-销售退货总额
			}
			BigDecimal costPrice = new BigDecimal(0);
			if(jvsp.getReCostPrice() != null){
				costPrice = jvsp.getCostPrice().subtract(jvsp.getReCostPrice()); // 实际进货金额=进货总额-进货退货总额
			}
			if(saleNum != 0){
				jvsp.setAvgSalePrice(salePrice.divide(new BigDecimal(saleNum),2,BigDecimal.ROUND_HALF_DOWN)); // 销售均价
			}
			if(costNum != 0){
				jvsp.setAvgCost(costPrice.divide(new BigDecimal(costNum),2,BigDecimal.ROUND_HALF_DOWN)); // 进货均价
			}
			jvsp.setSaleNum(saleNum);
			jvsp.setCostNum(costNum);
			// 计算盈利金额 公式:销售均价×销售数量 - 进货均价×销售数量
			if (!Validator.isEmpty(jvsp.getAvgSalePrice()) && !Validator.isEmpty(jvsp.getSaleNum())) {
				BigDecimal saleTotal = jvsp.getAvgSalePrice().multiply(new BigDecimal(jvsp.getSaleNum())); // 销售均价×销售数量 
				BigDecimal purChaseTotal = jvsp.getAvgCost().multiply(new BigDecimal(jvsp.getSaleNum())); // 进货均价×销售数量
				jvsp.setSalePrice(saleTotal);
				jvsp.setCostPrice(purChaseTotal);
				jvsp.setProfitPrice(saleTotal.subtract(purChaseTotal));
				result.add(jvsp);
			}
		}
		return result;
	}
	
	public Integer searchProfitCount(SearchVoucherCondition searchVoucherCondition) {
		Session session = this.getSession();
		StringBuilder proHql = new StringBuilder();
		// 查询该店铺所有商品
		proHql.append("select count(t.id) from Product t where 1=1 and t.status='" + Product.STATUS_NORMAL + "'");
		if(!Validator.isEmpty(searchVoucherCondition.getTreePath())){
			proHql.append(" and t.proClass.treePath like '%"+searchVoucherCondition.getTreePath()+"%'");
		}
		if(!Validator.isEmpty(searchVoucherCondition.getSearchProName())){
			proHql.append(" and t.name like '%"+searchVoucherCondition.getSearchProName().trim()+"%'");
		}
		proHql.append(" and t.store.id=" + searchVoucherCondition.getStoreid());
		
		Query query = session.createQuery(proHql.toString());
		return ((Long)query.uniqueResult()).intValue();
	}
	
	/**
	 * 根据商品ID获取该商品的库存
	 */
	public Integer getStockByProductId(SearchStockCondition searchStockConditon){
		Product pro = productDao.getProductById(searchStockConditon.getProductId());
		Integer stock = 0; // 库存数量
		stock = (getWareHouseNumByProId(searchStockConditon) - getReturnWareHouseNumByProId(searchStockConditon)) 
				- (getDeliveryNumByPorId(searchStockConditon) - getReturnDeliveryNumByProId(searchStockConditon));
		if(pro.getInitStock() != null) {
			stock = stock + pro.getInitStock();
		}
		return stock;
	}
	
	
	// 格式化查询库存的sql
	public void parseSearchStockHQL(StringBuilder sb,SearchStockCondition searchStockConditon){
		if(!Validator.isEmpty(searchStockConditon.getWareHouseId())){
			sb.append(" and v.voucher.wareHouse.id=" + searchStockConditon.getWareHouseId());
		}
		if (!Validator.isEmpty(searchStockConditon.getOrgId())) {
			sb.append(" and v.voucher.org.id =" + searchStockConditon.getOrgId());
		}
		if (!Validator.isEmpty(searchStockConditon.getStime())) {
			sb.append(" and v.voucher.datetime >= '" + searchStockConditon.getStime() + "'");
		}
		if (!Validator.isEmpty(searchStockConditon.getEtime())) {
			sb.append(" and v.voucher.datetime <= '" + searchStockConditon.getEtime() + "'");
		}
		if (!Validator.isEmpty(searchStockConditon.getProName())) {
			sb.append(" and v.product.name like '%" + searchStockConditon.getProName() + "%'");
		}
	}
	
	/**
	 * 获取商品的进货总数
	 */
	public Integer getWareHouseNumByProId(SearchStockCondition searchStockConditon){
		Session wareHouseNumSession = this.getSession();
		StringBuilder wareHouseNumHQL = new StringBuilder();
	    wareHouseNumHQL.append("SELECT SUM(v.amount) FROM ProductWareHouseVoucher v where 1=1");
	    wareHouseNumHQL.append(" and v.voucher.isverity= '" + VoucherVerityType.verityed + "'");
	    wareHouseNumHQL.append(" and v.product.id=" + searchStockConditon.getProductId());
	    parseSearchStockHQL(wareHouseNumHQL, searchStockConditon);
		Query wareHouseNumQuy = wareHouseNumSession.createQuery(wareHouseNumHQL.toString());
		List list = wareHouseNumQuy.list();
		if(list.get(0)!=null){
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}
	
	/**
	 * 获取商品的进货退货总数 
	 */
	public Integer getReturnWareHouseNumByProId(SearchStockCondition searchStockConditon){
		Session returnWareHouseSession = this.getSession();
		StringBuilder returnWareHouseNumHQL = new StringBuilder();
		returnWareHouseNumHQL.append("SELECT SUM(v.amount) FROM ProductReturnWareHouseVoucher v where 1=1");
		returnWareHouseNumHQL.append(" and v.voucher.isverity= '" + VoucherVerityType.verityed + "'");
		returnWareHouseNumHQL.append(" and v.product.id=" + searchStockConditon.getProductId());
		parseSearchStockHQL(returnWareHouseNumHQL, searchStockConditon);
		Query returnWareHouseNumQuy = returnWareHouseSession.createQuery(returnWareHouseNumHQL.toString());
		List list = returnWareHouseNumQuy.list();
		if(list.get(0)!=null){
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}
	
	/**
	 * 获取商品的销售总数 
	 */
	public Integer getDeliveryNumByPorId(SearchStockCondition searchStockConditon){
		Session deliveryNumSession = this.getSession();
		StringBuilder deliveryNumHQL = new StringBuilder();
		deliveryNumHQL.append("SELECT SUM(v.amount) FROM ProductDeliveryVoucher v where 1=1");
		deliveryNumHQL.append(" and v.voucher.isverity= '" + VoucherVerityType.verityed + "'");
		deliveryNumHQL.append(" and v.product.id=" + searchStockConditon.getProductId());
		parseSearchStockHQL(deliveryNumHQL, searchStockConditon);
		Query deliveryNumQuy = deliveryNumSession.createQuery(deliveryNumHQL.toString());
		List list = deliveryNumQuy.list();
		if(list.get(0)!=null){
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}
	
	/**
	 * 获取商品的销售退货总数
	 */
	public Integer getReturnDeliveryNumByProId(SearchStockCondition searchStockConditon){
		Session returnDeliverySession = this.getSession();
		StringBuilder returnDeliveryNumHQL = new StringBuilder();
		returnDeliveryNumHQL.append("SELECT SUM(v.amount) FROM ProductReturnDeliveryVoucher v where 1=1");
		returnDeliveryNumHQL.append(" and v.voucher.isverity= '" + VoucherVerityType.verityed + "'");
		returnDeliveryNumHQL.append(" and v.product.id=" + searchStockConditon.getProductId());
		parseSearchStockHQL(returnDeliveryNumHQL, searchStockConditon);
		Query returnDeliveryNumQuy = returnDeliverySession.createQuery(returnDeliveryNumHQL.toString());
		List list = returnDeliveryNumQuy.list();
		if(list.get(0)!=null){
			return Integer.parseInt(list.get(0).toString());
		}
		return 0;
	}

	public ProductDao getProductDao() {
		return productDao;
	}

	@Autowired
	public void setProductDao(ProductDao productDao) {
		this.productDao = productDao;
	}
}
